﻿use SSODemo;

--region Drop Existing Procedures

IF OBJECT_ID(N'[usp_InsertAccount]') IS NOT NULL
	DROP PROCEDURE [usp_InsertAccount]

IF OBJECT_ID(N'[usp_UpdateAccount]') IS NOT NULL
	DROP PROCEDURE [usp_UpdateAccount]

IF OBJECT_ID(N'[usp_SelectAccount]') IS NOT NULL
	DROP PROCEDURE [usp_SelectAccount]

IF OBJECT_ID(N'[usp_SelectAccountsDynamic]') IS NOT NULL
	DROP PROCEDURE [usp_SelectAccountsDynamic]

IF OBJECT_ID(N'[usp_SelectAccountsAll]') IS NOT NULL
	DROP PROCEDURE [usp_SelectAccountsAll]
	
IF OBJECT_ID(N'[usp_SelectAccountsPaged]') IS NOT NULL
	DROP PROCEDURE [usp_SelectAccountsPaged]

--endregion

GO

--region [usp_InsertAccount]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_InsertAccount]
-- Date Generated: 2009-04-25
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_InsertAccount]
	@RoleID uniqueidentifier,
	@Email varchar(50),
	@Name varchar(255),
	@Password varchar(50),
	@DateCreated datetime,
	@State int,
	@IsAdmin bit,
	@PictureAddress varchar(255),
	@Telephone varchar(20),
	@PassQuestion varchar(255),
	@PassAnswer varchar(255),
	@Remark varchar(255),
	@ID uniqueidentifier
AS

SET NOCOUNT ON

INSERT INTO [Account] (
	[ID],
	[RoleID],
	[Email],
	[Name],
	[Password],
	[DateCreated],
	[State],
	[IsAdmin],
	[PictureAddress],
	[Telephone],
	[PassQuestion],
	[PassAnswer],
	[Remark]
) VALUES (
	@ID,
	@RoleID,
	@Email,
	@Name,
	@Password,
	@DateCreated,
	@State,
	@IsAdmin,
	@PictureAddress,
	@Telephone,
	@PassQuestion,
	@PassAnswer,
	@Remark
)

--endregion

GO

--region [usp_UpdateAccount]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_UpdateAccount]
-- Date Generated: 2009-04-25
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_UpdateAccount]
	@ID uniqueidentifier,
	@RoleID uniqueidentifier,
	@Email varchar(50),
	@Name varchar(255),
	@Password varchar(50),
	@DateCreated datetime,
	@State int,
	@IsAdmin bit,
	@PictureAddress varchar(255),
	@Telephone varchar(20),
	@PassQuestion varchar(255),
	@PassAnswer varchar(255),
	@Remark varchar(255)
AS

SET NOCOUNT ON

UPDATE [Account] SET
	[RoleID] = @RoleID,
	[Email] = @Email,
	[Name] = @Name,
	[Password] = @Password,
	[DateCreated] = @DateCreated,
	[State] = @State,
	[IsAdmin] = @IsAdmin,
	[PictureAddress] = @PictureAddress,
	[Telephone] = @Telephone,
	[PassQuestion] = @PassQuestion,
	[PassAnswer] = @PassAnswer,
	[Remark] = @Remark
WHERE
	[ID] = @ID

--endregion

GO

--region [usp_SelectAccount]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_SelectAccount]
-- Date Generated: 2009-04-25
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_SelectAccount]
	@ID uniqueidentifier
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[RoleID],
	[Email],
	[Name],
	[Password],
	[DateCreated],
	[State],
	[IsAdmin],
	[PictureAddress],
	[Telephone],
	[PassQuestion],
	[PassAnswer],
	[Remark]
FROM
	[Account]
WHERE
	[ID] = @ID

--endregion

GO

--region [usp_SelectAccountsDynamic]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_SelectAccountsDynamic]
-- Date Generated: 2009-04-25
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_SelectAccountsDynamic]
	@WhereCondition nvarchar(500),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT
	[ID],
	[RoleID],
	[Email],
	[Name],
	[Password],
	[DateCreated],
	[State],
	[IsAdmin],
	[PictureAddress],
	[Telephone],
	[PassQuestion],
	[PassAnswer],
	[Remark]
FROM
	[Account]
WHERE
	' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + '
ORDER BY
	' + @OrderByExpression
END

EXEC sp_executesql @SQL

--endregion

GO

--region [usp_SelectAccountsAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_SelectAccountsAll]
-- Date Generated: 2009-04-25
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_SelectAccountsAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[RoleID],
	[Email],
	[Name],
	[Password],
	[DateCreated],
	[State],
	[IsAdmin],
	[PictureAddress],
	[Telephone],
	[PassQuestion],
	[PassAnswer],
	[Remark]
FROM
	[Account]
	
GO

--endregion

--region [usp_SelectAccountsAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   Zyg
-- Procedure Name: [usp_SelectAccountsPaged]
-- Date Generated: 2009-04-30
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [usp_SelectAccountsPaged]
	@StartIndex int,
	@EndIndex int
AS
BEGIN
	SET NOCOUNT ON;

	SELECT		
		[ID],
		[RoleID],
		[Email],
		[Name],
		[Password],
		[DateCreated],
		[State],
		[IsAdmin],
		[PictureAddress],
		[Telephone],
		[PassQuestion],
		[PassAnswer],
		[Remark],
		[RowNumber]
	FROM
	(
		SELECT
			[ID],
			[RoleID],
			[Email],
			[Name],
			[Password],
			[DateCreated],
			[State],
			[IsAdmin],
			[PictureAddress],
			[Telephone],
			[PassQuestion],
			[PassAnswer],
			[Remark],
			ROW_NUMBER() OVER(ORDER BY DateCreated) as [RowNumber]
		From [Account]
	) AS OrderedAccount
	WHERE RowNumber between @StartIndex and @EndIndex;
END

GO

--endregion
